package br.fatec.ads.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.fatec.ads.connection.DataSource;
import br.fatec.ads.exception.DataBaseException;
import br.fatec.ads.exception.VagaException;
import br.fatec.ads.model.Area;
import br.fatec.ads.model.ProcessoSeletivo;
import br.fatec.ads.model.ProcessoSeletivoPK;
import br.fatec.ads.model.Vaga;

public class VagaDAO {
	private static VagaDAO vagaDAO;
	private static String INCLUIR_VAGA = "EXEC P_VAGA_INCLUIR ?, ?, ?, ?, ?, ?";
	private static String ALTERAR_VAGA = "EXEC P_VAGA_ALTERAR ?, ?, ?, ?";
	private static String EXCLUIR_VAGA = "EXEC P_VAGA_EXCLUIR ?, ?";
	private static String CONSULTAR_VAGA = "EXEC P_VAGA_CONSULTAR ?";
	private static String LISTAR_VAGA = "EXEC P_VAGA_LISTAR ?, ?";
	private static String LISTAR_VAGA_EXCLUI_PROCESSO_CANDIDATURA = "EXEC P_VAGA_LISTAR_EXCLUI_PROCESSO_CANDIDATURA ?, ?, ?";
	
	public VagaDAO() {
		super();
	}
	
	public static VagaDAO getInstance(){
		if(vagaDAO == null){
			vagaDAO = new VagaDAO();
		}
		
		return vagaDAO;
	}
	
	public void incluirVaga(Vaga vaga) throws VagaException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(INCLUIR_VAGA);

                int i = 0;
                query.setInt(++i, vaga.getProcessoSeletivo().getIdProcessoSeletivo());
                query.setInt(++i, vaga.getArea().getIdArea());
                query.setString(++i, vaga.getCargoVaga());
                query.setString(++i, vaga.getDescVaga());
                query.setInt(++i, vaga.getQtdeVaga());
                query.setString(++i, vaga.getUsuario());
                
                query.executeUpdate();
            }catch(SQLException cause){
                throw new VagaException("Problemas ao INSERIR a Vaga!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        } catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
	
	public void alterarVaga(Vaga vaga) throws VagaException, DataBaseException {
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(ALTERAR_VAGA);

                int i = 0;
                query.setInt(++i, vaga.getIdVaga());
                query.setString(++i, vaga.getDescVaga());
                query.setInt(++i, vaga.getQtdeVaga());
                query.setString(++i, vaga.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new VagaException("Problemas ao ALTERAR a Vaga!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
	
	public void excluirVaga(Vaga vaga) throws VagaException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(EXCLUIR_VAGA);

                int i = 0;
                query.setInt(++i, vaga.getIdVaga());
                query.setString(++i, vaga.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new VagaException("Problemas ao EXCLUIR a Vaga!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
	
	public Vaga getVaga(int idVaga) throws VagaException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	Vaga vaga;
    	Area area;
    	ProcessoSeletivo processoSeletivo;
    	
    	try{
    		try{
    			vaga = new Vaga();
    			
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(CONSULTAR_VAGA);
    			
    			int i = 0;
    			query.setInt(++i, idVaga);
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				area = new Area();
    				area.setIdArea(resultSet.getInt("IDAREA"));
    				area.setDescArea(resultSet.getString("DESCAREA"));
    				
    				processoSeletivo = new ProcessoSeletivo(resultSet.getInt("IDPROCESSOSELETIVO"));
    				processoSeletivo.setDescProcessoSeletivo(resultSet.getString("DESCPROCESSOSELETIVO"));
    				processoSeletivo.setNomeProcessoSeletivo(resultSet.getString("NOMEPROCESSOSELETIVO"));
    				
    				vaga.setIdVaga(resultSet.getInt("IDVAGA"));
    				vaga.setArea(area);
    				vaga.setProcessoSeletivo(processoSeletivo);
    				vaga.setCargoVaga(resultSet.getString("CARGOVAGA"));
    				vaga.setDescVaga(resultSet.getString("DESCVAGA"));
    				vaga.setQtdeVaga(resultSet.getInt("QTDEVAGAS"));
    				vaga.setAtivo(resultSet.getString("ATIVO"));
    				vaga.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				vaga.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    			}
    		}catch (SQLException cause) {
    			throw new VagaException("Problemas ao CONSULTAR a Vaga!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return vaga;
    }
	
	public List listarVaga(Vaga vaga) throws VagaException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	List listaVaga = new ArrayList();
    	Area area;
    	ProcessoSeletivo processoSeletivo;
    	
    	try{
    		try{
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(LISTAR_VAGA);
    			
    			int i = 0;
    			query.setInt(++i, vaga.getProcessoSeletivo().getIdProcessoSeletivo());
                query.setInt(++i, vaga.getArea().getIdArea());
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				Vaga vagas = new Vaga();
    				
    				area = new Area();
    				area.setIdArea(resultSet.getInt("IDAREA"));
    				area.setDescArea(resultSet.getString("DESCAREA"));
    				
    				processoSeletivo = new ProcessoSeletivo(resultSet.getInt("IDPROCESSOSELETIVO"));
    				processoSeletivo.setDescProcessoSeletivo(resultSet.getString("DESCPROCESSOSELETIVO"));
    				processoSeletivo.setNomeProcessoSeletivo(resultSet.getString("NOMEPROCESSOSELETIVO"));
    				
    				vagas.setIdVaga(resultSet.getInt("IDVAGA"));
    				vagas.setArea(area);
    				vagas.setProcessoSeletivo(processoSeletivo);
    				vagas.setCargoVaga(resultSet.getString("CARGOVAGA"));
    				vagas.setDescVaga(resultSet.getString("DESCVAGA"));
    				vagas.setQtdeVaga(resultSet.getInt("QTDEVAGAS"));
    				vagas.setAtivo(resultSet.getString("ATIVO"));
    				vagas.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				vagas.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    				
    				listaVaga.add(vagas);
    			}
    		}catch (SQLException cause) {
    			throw new VagaException("Problemas ao LISTAR a Vaga!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return listaVaga;
    }
	
	public List listarVagaExcluiProcessoCandidatura(Vaga vaga, int idUsuario) throws VagaException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	List listaVaga = new ArrayList();
    	Area area;
    	ProcessoSeletivo processoSeletivo;
    	
    	try{
    		try{
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(LISTAR_VAGA_EXCLUI_PROCESSO_CANDIDATURA);
    			
    			int i = 0;
    			query.setInt(++i, vaga.getProcessoSeletivo().getIdProcessoSeletivo());
                query.setInt(++i, vaga.getArea().getIdArea());
                query.setInt(++i, idUsuario);
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				Vaga vagas = new Vaga();
    				
    				area = new Area();
    				area.setIdArea(resultSet.getInt("IDAREA"));
    				area.setDescArea(resultSet.getString("DESCAREA"));
    				
    				processoSeletivo = new ProcessoSeletivo(resultSet.getInt("IDPROCESSOSELETIVO"));
    				processoSeletivo.setDescProcessoSeletivo(resultSet.getString("DESCPROCESSOSELETIVO"));
    				processoSeletivo.setNomeProcessoSeletivo(resultSet.getString("NOMEPROCESSOSELETIVO"));
    				
    				vagas.setIdVaga(resultSet.getInt("IDVAGA"));
    				vagas.setArea(area);
    				vagas.setProcessoSeletivo(processoSeletivo);
    				vagas.setCargoVaga(resultSet.getString("CARGOVAGA"));
    				vagas.setDescVaga(resultSet.getString("DESCVAGA"));
    				vagas.setQtdeVaga(resultSet.getInt("QTDEVAGAS"));
    				vagas.setAtivo(resultSet.getString("ATIVO"));
    				vagas.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				vagas.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    				
    				listaVaga.add(vagas);
    			}
    		}catch (SQLException cause) {
    			throw new VagaException("Problemas ao LISTAR a Vaga!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return listaVaga;
    }
}